/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Category;
import model.Product;
import model.Staff;

/**
 *
 * @author Trang
 */
public class ProductManagerDAO {
    private Connection con;
    public ProductDAO productDao = new ProductDAO();

    public ProductManagerDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }
    public List<Product> GetAllProduct() {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT * FROM product";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public void updateProduct(Product product)
    {
        try
        {           
            String sql = "UPDATE product set name=?,"
                    + "summary=?,details=?,"
                    + "brand=?,others=?,unitPrice=?,"
                    + "stockQuantity=?,sale=? where idStaff= "+product.getIdProduct();
            PreparedStatement ps = con.prepareStatement(sql);
            
            //ps.setInt(1, sid);
            ps.setString(2, product.getName());
            ps.setString(3,product.getSummary());
            ps.setString(4, product.getDetails());
            ps.setString(5, product.getOthers());
            ps.setDouble(6, product.getUnitPrice());
            ps.setDouble(7, product.getStockQuantity());
            ps.setDouble(8, product.getSale());       
            ps.executeQuery();
            con.commit();
            ps.close();
            con.close();
        }
        catch(Exception e)
        {
            
        }
    }
    
     public List<Category> getChildrenCategory() {
        List<Category> result = new ArrayList<Category>();

        try {
            //get parent categories
            String sqlst = "SELECT * FROM category WHERE parentid IS NOT NULL";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");
                
                Integer idParent = rs.getInt("parentid");
                System.out.println(idParent);
                Category temp = new Category(idCategory, name, description);

                //get children of each parent
                
                String sqlst2 = "SELECT * FROM category WHERE idCategory = ?";
                PreparedStatement ps2 = con.prepareStatement(sqlst2);
                ps2.setInt(1, idParent);
                ResultSet rs2 = ps2.executeQuery();

                while (rs2.next()) {
                    Integer idCategory2 = rs2.getInt("idCategory");
                    String name2 = rs2.getString("name");
                    String description2 = rs2.getString("description"); 
                    Category parentCategory = new Category(idCategory2, name2, description2);              
                    temp.setParentCategory(parentCategory);    
                    System.out.println(parentCategory);
                }            
                
                //add to all
                result.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    } 
     
     public List<Category> getParentCategory() {
        List<Category> result = new ArrayList<Category>();

        try {
            //get parent categories
            String sqlst = "SELECT * FROM category WHERE parentid IS NULL";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                Category temp = new Category(idCategory, name, description);

                result.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
     
     public Category getCategoryFromName(String CategoryName) {
        Category result = new Category();
        //boolean test = false;

        try {
            String sqlst = "SELECT * FROM category WHERE name = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, CategoryName);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                result = new Category(idCategory, name, description);
                //test = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
        //return test;
    }
     
     public Category getCategoryFromId(Integer cid) {
        Category result = new Category();

        try {
            String sqlst = "SELECT * FROM category WHERE idCategory = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, cid);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                result = new Category(idCategory, name, description);
                //test = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
        //return test;
    }
     
     public boolean addParentCategory(Category category) {
        boolean result = false;

        try {
            String sqlst;
            PreparedStatement ps;
                sqlst = "INSERT INTO category(name, description) VALUES (?, ?)";
                ps = con.prepareStatement(sqlst);
                ps.setString(1, category.getName());
                ps.setString(2, category.getDescription());
            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
     public boolean addChildCategory(Category category) {
        boolean result = false;

        try {
            String sqlst;
            PreparedStatement ps;

            
                sqlst = "INSERT INTO category(name, description, parentid) VALUES (?, ?, ?)";
                ps = con.prepareStatement(sqlst);
                ps.setString(1, category.getName());
                ps.setString(2, category.getDescription());
                ps.setInt(3, category.getParentCategory().getIdCategory());

            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
     public boolean deleteCategory(Integer cid)
     {
         boolean result = false;
         List<Product> products = new ArrayList<Product>();
         try
         {
         String sqlst2 = "SELECT * FROM category WHERE parentid = ?";
                PreparedStatement ps2 = con.prepareStatement(sqlst2);
                ps2.setInt(1, cid);
                ResultSet rs2 = ps2.executeQuery();

                while (rs2.next()) {
                    Integer idCategory2 = rs2.getInt("idCategory");         
                    String slqchild = "DELETE FROM category WHERE idCategory = ?";
                    PreparedStatement ps3 = con.prepareStatement(sqlst2);
                    ps3.setInt(1, idCategory2);
                    ps3.executeUpdate();  
                }
         //Get product belong category
         products = productDao.getProductsFromCategoryID(cid);
         for(Product item: products)
         {
             DeleteProductByProductID(item.getIdProduct());
         }
         //Delete category
         String sqlst = "DELETE FROM category WHERE idCategory = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, cid);
            ps.executeUpdate();
            result = true;
                
         //DELETE FROM Products WHERE ProductName = 'MyProduct'
         } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
         return result;
     }
      public boolean DeleteProductByProductID(int pid) {
          boolean result = false;
        try {
            String sqlst = "DELETE FROM product WHERE idProduct = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, pid);
            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
      
      public List<Product> getAllProducts( int offset, int length) {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT * FROM product"                
                    + " LIMIT ?, ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, offset);
            ps.setInt(2, length);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
     public Integer getLastidProduct()
     {
         Integer result = 0;
         try {
            String sqlst = "SELECT idProduct FROM product ORDER BY idProduct DESC"                
                    + " LIMIT 1";
            PreparedStatement ps = con.prepareStatement(sqlst);
            

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                result  = rs.getInt("idProduct");             
                //result.add(new Product(idProduct, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

         return result;
     }
     public List<Category> searchForCategoryByKeyword(String keyword) {
        List<Category> result = new ArrayList<Category>();

        try {
            String sqlst = "SELECT * FROM category"
                    + " WHERE name LIKE ?"
                    + " LIMIT 36";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "%" + keyword + "%");
            
            
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                Category cate = new Category(idCategory, name, description);
                result.add(cate);
                //test = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
}
